package cn.itcast.dao;



import cn.itcast.domain.CourseAndSc;
import cn.itcast.domain.Sc;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ScDao {

        // 根据学号和课程号查询学生是否选择了该课程
        @Select("SELECT * FROM sc WHERE sno = #{username}")
        public List<Sc> findByIdToSc(@Param("username")String username);

        // 根据学号和课程号添加课程
        @Insert("insert into sc(sno,courseno) value(#{username},#{courseno})")
        public void insertToSc(@Param("username")String username,
                               @Param("courseno")Integer courseno);

        // 根据学号查询课程
        @Select("SELECT sc.`sno`,sc.`courseno`,course.`coursename`,sc.`score` FROM " +
                "sc,course WHERE sc.`courseno` = course.`courseno` " +
                "AND sc.`sno` = #{username}")
        public List<CourseAndSc> findAllByStuId(@Param("username")String username);

        // 根据学号和课号删除已选课程
        @Delete("DELETE FROM sc WHERE sno = #{username} AND courseno  = #{courseno}")
        public void deleteToSnoAndCourseno(@Param("username")String username,
                @Param("courseno")Integer courseno);

}
